package com.chidopi.market.bookstore.dao.impl;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Arrays;
import java.util.Date;
import java.util.List;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.core.simple.ParameterizedBeanPropertyRowMapper;
import org.springframework.jdbc.core.simple.ParameterizedRowMapper;

import com.chidopi.cdf.dao.AbstractBaseDaoImpl;
import com.chidopi.market.bookstore.dao.IBookItemsDao;
import com.chidopi.market.bookstore.domain.BookCategories;
import com.chidopi.market.bookstore.domain.BookItems;
import com.chidopi.market.bookstore.domain.BookLang;



public class BookItemsDaoImpl extends AbstractBaseDaoImpl<BookItems> implements IBookItemsDao{
	
	private static Logger logger = LoggerFactory.getLogger(BookItemsDaoImpl.class);

	@Override
	public int insert(BookItems t) {
		String sql = "insert into bookitems (BOOKNAME, DESCRIPTION, ICONIMAGE, THUMBIMAGE, ALBUM_FLAG, ZIPFILE, ZIPFILENAME, BOOKSIZE, ENABLE_FLAG, KEYWORD, RELEASE_DATE, CREATE_DATE, CREATE_BY) "
				   + "values (:bookname, :description, :iconImage, :thumbImage, :album_flag, :zipfile, :zipFileName, :booksize, :enable_flag, :keyword, :release_date, NOW(), :create_by)";
		int ret = super.insert(sql, t);
		
		if (ret > 0) {
			t.setId(ret);
			updateDetails(t,false);
		}
		return ret;
	}

	private void updateDetails(BookItems t, boolean isUpdate) {
		
		if(isUpdate){
		  String sql ="delete from book2category where BOOK_ID = ? ";
		  super.simpleJdbcTemplate.update(sql, t.getId());
		  
		  sql = "delete from booklang where Book_ID = ? ";
		  super.simpleJdbcTemplate.update(sql, t.getId());
		  
		  sql = "delete from book2album where ALBUM_ID = ? ";
		  super.simpleJdbcTemplate.update(sql, t.getId());
		  
		  if(t.getOldfile() != null && t.getOldfile().trim().length()>0){
			  sql = "insert into bookzipbackup (ZIPFILE, ZIPFILENAME, BOOK_ID, CREATE_TIME) " +
			  		" values (?, ?, ?, ?) ";
			  super.simpleJdbcTemplate.update(sql, t.getOldfile(),t.getOldfilename(),t.getId(), new Date());
		  }
		}
		
		List<BookCategories> categories = t.getCategories();
		if(null != categories){
			String sql = "insert into book2category (BOOK_ID, CATEGORY_ID) values (?, ?)";
			for (BookCategories category : categories) {
				super.simpleJdbcTemplate.update(sql, t.getId(), category.getId());
			}
		}		
		List<BookLang> langs = t.getBookLangs();
		if(null != langs){
			String sql = "insert into booklang (Book_ID, LANG, BOOKNAME) values (?, ?, ?)";
			for(BookLang lang :langs){
				super.simpleJdbcTemplate.update(sql, t.getId(), lang.getLang(), lang.getBookname());
			}
		}
		logger.debug("Books:  " + t.getBooks());
		List<BookItems> books = t.getBooks();
		if(null != books){
			String sql = "insert into book2album (ALBUM_ID ,Book_ID) values (?, ?)";
			for(BookItems book : books){
				super.simpleJdbcTemplate.update(sql, t.getId(), book.getId());
			}
		}		
	}

	@Override
	public int update(BookItems t) {
		String sql = "update bookitems set BOOKNAME = :bookname, DESCRIPTION = :description, ICONIMAGE = :iconImage, THUMBIMAGE=:thumbImage, ALBUM_FLAG = :album_flag, " +
				     "ZIPFILE = :zipfile, ZIPFILENAME = :zipFileName, BOOKSIZE = :booksize, ENABLE_FLAG = :enable_flag, KEYWORD = :keyword, RELEASE_DATE = :release_date, update_by = :update_by where id= :id";
		int ret = super.execSQL(sql, t);
		if (ret > 0) this.updateDetails(t,true);
		return ret;
	}

	@Override
	public int delete(BookItems t) {
		
		//DB bookitems, book2categories, booklang, book2album, book2city, zipbackup		
		Integer id = t.getId();
		
		logger.debug("delete booitems id: " + id );
		
		String sql = "delete from bookitems where id = :id";
		int ret =  super.execSQL(sql, t);
			
		
		if (ret > -1) {
			logger.debug("delete book2category id: " + id );
			sql = "delete from book2category where BOOK_ID = ? ";
			this.simpleJdbcTemplate.update(sql, id);
			
			logger.debug("delete booklang id: " + id );
			sql = "delete from booklang where Book_ID = ?";
			this.simpleJdbcTemplate.update(sql, id);
			
			if(t.getAlbum_flag().equals("Y")){
				logger.debug("delete book2album id: " + id );
				sql = "delete from book2album where ALBUM_ID = ?";
				this.simpleJdbcTemplate.update(sql, id);
			}
			
			logger.debug("delete bookzipbackup id: " + id );
			sql = "delete from bookzipbackup where BOOK_ID = ?";
			this.simpleJdbcTemplate.update(sql, id);
			
			logger.debug("delete book2city id: " + id );
			sql = "delete from book2city where BOOK_ID = ?";
			this.simpleJdbcTemplate.update(sql, id);
		}
		return ret;
	}

	@Override
	public List<BookItems> find(String sqlWhere, Object... args) {
		if (sqlWhere == null || sqlWhere.trim().equals("")) {
			String sql = "select * from bookitems order by id ";
			return super.simpleJdbcTemplate.query(sql, this.getRowMapper());
		} else {
			logger.debug("sqlWhere:[" + sqlWhere + "]/params:" + Arrays.asList(args).toString());
			String sql = "select * from bookitems where " + sqlWhere + " order by id";
			logger.debug("sql: " + sql);
			List<BookItems> list = super.simpleJdbcTemplate.query(sql, this.getRowMapper(), args);
			if (list == null) {
				logger.debug("query null!!!");
			} else {
				for (BookItems book : list) {
					logger.debug("id=" + book.getId() + "/bookname=" + book.getBookname());
				}
			}
			return list;
		}
	}

	@Override
	public BookItems findById(Object id) {
		String sql = "id=?";
		List<BookItems> list = this.find(sql, id);
		if (0 == list.size()) return null;
		return list.get(0);
	}
	

	@Override
	public List<BookLang> findBookLangs(String sql, Object... args) {		
		
		List<BookLang> list = super.simpleJdbcTemplate.query(sql, this.getBookLangRowMapper(), args);
		return list;
	}

	
	private RowMapper<BookLang> getBookLangRowMapper() {
		return  new ParameterizedRowMapper<BookLang>(){
			@Override
			public BookLang mapRow(ResultSet rs, int rowNum)
					throws SQLException {
				BookLang lang = new BookLang();
				lang.setLang(rs.getString("LANG"));
				lang.setBookname(rs.getString("BOOKNAME"));
				return lang;
			}		
		};
	}
	
	@Override
	public List<String> findBackupZipById(Object id) {
		String sql = "select * from bookzipbackup where BOOK_ID = ?";
		List<String> list  = super.simpleJdbcTemplate.query(sql, getBackupZipRowMapper(), id);
		return list;
	}
	
	private RowMapper<String> getBackupZipRowMapper(){
		return  new ParameterizedRowMapper<String>(){

			@Override
			public String mapRow(ResultSet rs, int rowNum) throws SQLException {				
				return rs.getString("ZIPFILE");
			}
			
		};				
				
	}
	
	

//	@Override
//	public byte[] findIconImageById(Object id) {
//		String sql = "select ICONIMAGE from bookitems where id = :id";
//		BookItems vo=  super.simpleJdbcTemplate.queryForObject(sql, this.getIconImage(), id);				
//		return  null != vo ? vo.getIconImage() : null;
//	}	
//
//	@Override
//	public byte[] findThumbImageById(Object id) {
//		String sql = "select THUMBIMAGE from bookitems where id = :id";
//		BookItems vo=  super.simpleJdbcTemplate.queryForObject(sql, this.getThumbImage(), id);				
//		return  null != vo ? vo.getThumbImage() : null;
//	}

	@Override
	protected String getTable() {
		return "bookitems";
	}

	@Override
	protected String getSequenceName() {
		return null;
	}
	
//	protected RowMapper<BookItems> getIconImage(){
//		return new ParameterizedRowMapper<BookItems>(){
//			@Override
//			public BookItems mapRow(ResultSet rs, int rowNum)	throws SQLException {
//				BookItems vo = new BookItems();
//				Blob b = rs.getBlob("ICONIMAGE");
//				long size = b.length();
//				byte[] bs = b.getBytes(1, (int)size);
//				vo.setIconImage(bs);				
//                return vo;
//			}
//		};
//	}
//	
//	protected RowMapper<BookItems> getThumbImage(){
//		return new ParameterizedRowMapper<BookItems>(){
//			@Override
//			public BookItems mapRow(ResultSet rs, int rowNum)	throws SQLException {
//				BookItems vo = new BookItems();
//				Blob b = rs.getBlob("THUMBIMAGE");
//				long size = b.length();
//				byte[] bs = b.getBytes(1, (int)size);
//				vo.setThumbImage(bs);				
//                return vo;
//			}
//		};
//	}

	@Override
	protected RowMapper<BookItems> getRowMapper() {
		return ParameterizedBeanPropertyRowMapper.newInstance(BookItems.class);
//		return new ParameterizedRowMapper<BookItems>(){
//
//			@Override
//			public BookItems mapRow(ResultSet rs, int rowNum)	throws SQLException {
//				BookItems vo = new BookItems();
//				vo.setId(rs.getInt("ID"));
//				vo.setBookname(rs.getString("BOOKNAME"));
//				vo.setDescription(rs.getString("DESCRIPTION"));
//				vo.setCpName(rs.getString("CPNAME"));
//				vo.setCpDescription(rs.getString("CPDESCRIPTION"));
//				vo.setCpCode(rs.getString("CPCODE"));
//				Brand brand = new Brand();
//				brand.setId(rs.getInt("BRAND_ID"));
//				brand.setBrandname(rs.getString("BRANDNAME"));
//				vo.setBrand(brand);
//				vo.setCreate_date(rs.getDate("CREATE_DATE"));
//	            vo.setCreate_by(rs.getInt("CREATE_BY"));
//	            vo.setUpdate_date(rs.getDate("UPDATE_DATE"));
//	            vo.setUpdate_by(rs.getInt("UPDATE_BY"));
//				return vo;
//			}
//		};	
    }





}
